#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
三角洲数据插入脚本
用于将S5数据JSON文件中的数据插入到MySQL数据库中
"""

import json
import mysql.connector
from mysql.connector import Error
import sys
import os
from datetime import datetime

class DeltaDataImporter:
    def __init__(self, host='localhost', database='delta_operation_s5', user='root', password=''):
        self.host = host
        self.database = database
        self.user = user
        self.password = password
        self.connection = None
        self.cursor = None

    def connect(self):
        """连接到MySQL数据库"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                database=self.database,
                user=self.user,
                password=self.password,
                charset='utf8mb4'
            )
            if self.connection.is_connected():
                self.cursor = self.connection.cursor()
                print(f"成功连接到MySQL数据库: {self.database}")
                return True
        except Error as e:
            print(f"连接数据库时出错: {e}")
            return False

    def disconnect(self):
        """断开数据库连接"""
        if self.connection and self.connection.is_connected():
            self.cursor.close()
            self.connection.close()
            print("数据库连接已关闭")

    def clear_tables(self):
        """清空所有表数据"""
        try:
            # 禁用外键检查
            self.cursor.execute("SET FOREIGN_KEY_CHECKS = 0")

            tables = [
                'item_effects', 'item_usage', 'item_attributes', 'item_details', 'items'
            ]

            for table in tables:
                self.cursor.execute(f"TRUNCATE TABLE {table}")
                print(f"已清空表: {table}")

            # 重新启用外键检查
            self.cursor.execute("SET FOREIGN_KEY_CHECKS = 1")
            self.connection.commit()
            print("所有表数据已清空")
        except Error as e:
            print(f"清空表时出错: {e}")
            self.connection.rollback()

    def insert_item(self, item_data):
        """插入物品基本信息"""
        try:
            insert_query = """
            INSERT INTO items (
                id, object_id, object_name, length, width, grade, weight,
                primary_class, second_class, second_class_cn, third_class, third_class_cn,
                description, pic, pre_pic, avg_price
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """

            values = (
                item_data.get('id'),
                item_data.get('objectID'),
                item_data.get('objectName', ''),
                item_data.get('length'),
                item_data.get('width'),
                item_data.get('grade'),
                str(item_data.get('weight', '')),
                item_data.get('primaryClass', ''),
                item_data.get('secondClass'),
                item_data.get('secondClassCN'),
                item_data.get('thirdClass'),
                item_data.get('thirdClassCN'),
                item_data.get('desc'),
                item_data.get('pic'),
                item_data.get('prePic'),
                item_data.get('avgPrice', 0)
            )

            self.cursor.execute(insert_query, values)
            return True
        except Error as e:
            print(f"插入物品 {item_data.get('objectName', 'Unknown')} 时出错: {e}")
            return False
    def insert_item_detail(self, item_id, detail_type, detail_data):
        """插入物品详细信息（使用JSON存储）"""
        try:
            insert_query = """
            INSERT INTO item_details (item_id, detail_type, detail_data)
            VALUES (%s, %s, %s)
            ON DUPLICATE KEY UPDATE detail_data = VALUES(detail_data)
            """

            values = (item_id, detail_type, json.dumps(detail_data, ensure_ascii=False))
            self.cursor.execute(insert_query, values)
            return True
        except Error as e:
            print(f"插入物品详细信息时出错: {e}")
            return False

    def insert_item_attribute(self, item_id, attr_name, attr_value, attr_type='text', display_order=0):
        """插入物品属性"""
        try:
            insert_query = """
            INSERT INTO item_attributes (item_id, attribute_name, attribute_value, attribute_type, display_order)
            VALUES (%s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                attribute_value = VALUES(attribute_value),
                attribute_type = VALUES(attribute_type),
                display_order = VALUES(display_order)
            """

            values = (item_id, attr_name, str(attr_value), attr_type, display_order)
            self.cursor.execute(insert_query, values)
            return True
        except Error as e:
            print(f"插入物品属性时出错: {e}")
            return False

    def insert_item_effect(self, item_id, effect_type, effect_name, effect_value, battery_value=None, battery_color=None, display_order=0):
        """插入物品效果"""
        try:
            insert_query = """
            INSERT INTO item_effects (
                item_id, effect_type, effect_name, effect_value,
                battery_value, battery_color, display_order
            ) VALUES (%s, %s, %s, %s, %s, %s, %s)
            """

            values = (item_id, effect_type, effect_name, effect_value, battery_value, battery_color, display_order)
            self.cursor.execute(insert_query, values)
            return True
        except Error as e:
            print(f"插入物品效果时出错: {e}")
            return False

    def insert_item_usage(self, item_id, usage_type, usage_map=None, usage_place=None, usage_description=None, active_time=None):
        """插入物品使用信息"""
        try:
            insert_query = """
            INSERT INTO item_usage (
                item_id, usage_type, usage_map, usage_place, usage_description, active_time
            ) VALUES (%s, %s, %s, %s, %s, %s)
            """

            values = (item_id, usage_type, usage_map, usage_place, usage_description, active_time)
            self.cursor.execute(insert_query, values)
            return True
        except Error as e:
            print(f"插入物品使用信息时出错: {e}")
            return False

    def process_protect_detail(self, item_id, protect_data):
        """处理防护装备详细信息"""
        try:
            # 插入JSON详细信息
            self.insert_item_detail(item_id, 'protect', protect_data)

            # 插入关键属性
            if 'durability' in protect_data:
                self.insert_item_attribute(item_id, 'durability', protect_data['durability'], 'numeric', 1)
            if 'protectLevel' in protect_data:
                self.insert_item_attribute(item_id, 'protect_level', protect_data['protectLevel'], 'numeric', 2)
            if 'capacity' in protect_data:
                self.insert_item_attribute(item_id, 'capacity', protect_data['capacity'], 'numeric', 3)

            # 处理速度效果
            if 'aimSpeed' in protect_data:
                aim_speed = protect_data['aimSpeed']
                if 'percent' in aim_speed:
                    self.insert_item_effect(item_id, 'aim_speed', '瞄准速度', f"{aim_speed['percent']}%",
                                          aim_speed.get('batteryValue'), aim_speed.get('batteryColor'), 1)

            if 'moveSpeed' in protect_data:
                move_speed = protect_data['moveSpeed']
                if 'percent' in move_speed:
                    self.insert_item_effect(item_id, 'move_speed', '移动速度', f"{move_speed['percent']}%",
                                          move_speed.get('batteryValue'), move_speed.get('batteryColor'), 2)

            return True
        except Exception as e:
            print(f"处理防护装备详细信息时出错: {e}")
            return False

    def process_accessory_detail(self, item_id, acc_data):
        """处理配件详细信息"""
        try:
            # 插入JSON详细信息
            self.insert_item_detail(item_id, 'acc', acc_data)

            # 插入关键属性
            if 'controlSpeed' in acc_data:
                self.insert_item_attribute(item_id, 'control_speed', acc_data['controlSpeed'], 'numeric', 1)
            if 'controlStable' in acc_data:
                self.insert_item_attribute(item_id, 'control_stable', acc_data['controlStable'], 'numeric', 2)

            # 处理优势效果
            advantages = acc_data.get('advantage', {}).get('effectList', [])
            for i, advantage in enumerate(advantages):
                self.insert_item_effect(item_id, 'advantage', '优势效果', advantage.get('value'),
                                      advantage.get('batteryValue'), advantage.get('batteryColor'), i+1)

            # 处理劣势效果
            disadvantages = acc_data.get('disadvantage', {}).get('effectList', [])
            for i, disadvantage in enumerate(disadvantages):
                self.insert_item_effect(item_id, 'disadvantage', '劣势效果', disadvantage.get('value'),
                                      disadvantage.get('batteryValue'), disadvantage.get('batteryColor'), i+1)

            return True
        except Exception as e:
            print(f"处理配件详细信息时出错: {e}")
            return False

    def process_ammo_detail(self, item_id, ammo_data):
        """处理弹药详细信息"""
        try:
            # 插入JSON详细信息
            self.insert_item_detail(item_id, 'ammo', ammo_data)

            # 插入关键属性
            if 'penetrationLevel' in ammo_data:
                self.insert_item_attribute(item_id, 'penetration_level', ammo_data['penetrationLevel'], 'numeric', 1)
            if 'harmRatio' in ammo_data:
                self.insert_item_attribute(item_id, 'harm_ratio', ammo_data['harmRatio'], 'numeric', 2)
            if 'armorHarmLevel' in ammo_data:
                self.insert_item_attribute(item_id, 'armor_harm_level', ammo_data['armorHarmLevel'], 'text', 3)

            return True
        except Exception as e:
            print(f"处理弹药详细信息时出错: {e}")
            return False

    def process_props_detail(self, item_id, props_data):
        """处理道具详细信息"""
        try:
            # 插入JSON详细信息
            self.insert_item_detail(item_id, 'props', props_data)

            # 插入关键属性
            if 'repairPoints' in props_data:
                self.insert_item_attribute(item_id, 'repair_points', props_data['repairPoints'], 'numeric', 1)
            if 'durability' in props_data:
                self.insert_item_attribute(item_id, 'durability', props_data['durability'], 'numeric', 2)

            # 插入使用信息
            if any(key in props_data for key in ['useMap', 'usePlace', 'activeTime']):
                self.insert_item_usage(item_id, 'props',
                                     props_data.get('useMap'),
                                     props_data.get('usePlace'),
                                     props_data.get('replyEffect'),
                                     props_data.get('activeTime'))

            return True
        except Exception as e:
            print(f"处理道具详细信息时出错: {e}")
            return False
    def process_item(self, item_data):
        """处理单个物品数据"""
        try:
            # 插入基本物品信息
            if not self.insert_item(item_data):
                return False

            item_id = item_data.get('id')
            primary_class = item_data.get('primaryClass', '')

            # 根据物品类型处理详细信息
            if primary_class == 'protect' and 'protectDetail' in item_data:
                self.process_protect_detail(item_id, item_data['protectDetail'])

            elif primary_class == 'acc' and 'accDetail' in item_data:
                self.process_accessory_detail(item_id, item_data['accDetail'])

            elif primary_class == 'ammo' and 'ammoDetail' in item_data:
                self.process_ammo_detail(item_id, item_data['ammoDetail'])

            elif primary_class == 'props' and 'propsDetail' in item_data:
                self.process_props_detail(item_id, item_data['propsDetail'])

            # 处理其他类型的详细信息
            elif primary_class in ['skin', 'exchange', 'vehicle', 'opProps']:
                # 对于其他类型，直接存储为JSON
                detail_key = f"{primary_class}Detail"
                if detail_key in item_data:
                    self.insert_item_detail(item_id, primary_class, item_data[detail_key])

            return True
        except Exception as e:
            print(f"处理物品 {item_data.get('objectName', 'Unknown')} 时出错: {e}")
            return False

    def import_data(self, json_file_path):
        """导入JSON数据"""
        try:
            print(f"开始读取JSON文件: {json_file_path}")

            with open(json_file_path, 'r', encoding='utf-8') as f:
                data = json.load(f)

            # 获取物品列表
            items_list = data.get('jData', {}).get('data', {}).get('data', {}).get('list', [])

            if not items_list:
                print("JSON文件中没有找到物品数据")
                return False

            print(f"找到 {len(items_list)} 个物品，开始导入...")

            success_count = 0
            error_count = 0

            for i, item in enumerate(items_list, 1):
                if self.process_item(item):
                    success_count += 1
                else:
                    error_count += 1

                if i % 100 == 0:
                    print(f"已处理 {i}/{len(items_list)} 个物品")
                    self.connection.commit()  # 每100个物品提交一次

            # 最终提交
            self.connection.commit()

            print(f"\n导入完成!")
            print(f"成功导入: {success_count} 个物品")
            print(f"导入失败: {error_count} 个物品")

            return True

        except FileNotFoundError:
            print(f"文件不存在: {json_file_path}")
            return False
        except json.JSONDecodeError as e:
            print(f"JSON文件格式错误: {e}")
            return False
        except Exception as e:
            print(f"导入数据时出错: {e}")
            return False

def main():
    """主函数"""
    # 配置数据库连接信息
    DB_CONFIG = {
        'host': 'localhost',
        'database': 'delta_operation_s5',
        'user': 'root',
        'password': ''  # 请根据实际情况修改密码
    }

    # JSON文件路径
    json_file = '三角洲数据合集/S5数据/s5数据.json'

    if not os.path.exists(json_file):
        print(f"JSON文件不存在: {json_file}")
        print("请确保文件路径正确")
        return

    # 创建导入器实例
    importer = DeltaDataImporter(**DB_CONFIG)

    try:
        # 连接数据库
        if not importer.connect():
            return

        # 询问是否清空现有数据
        response = input("是否清空现有数据? (y/N): ").strip().lower()
        if response == 'y':
            importer.clear_tables()

        # 导入数据
        start_time = datetime.now()
        print(f"\n开始导入数据: {start_time}")

        if importer.import_data(json_file):
            end_time = datetime.now()
            duration = end_time - start_time
            print(f"导入完成: {end_time}")
            print(f"总耗时: {duration}")
        else:
            print("数据导入失败")

    except KeyboardInterrupt:
        print("\n用户中断操作")
    except Exception as e:
        print(f"程序执行出错: {e}")
    finally:
        importer.disconnect()

if __name__ == '__main__':
    main()
